package com.qymerge.manage.data.dao.impl;

import com.qymerge.manage.core.bean.PageRequest;
import com.qymerge.manage.core.constant.ParamKey;
import com.qymerge.manage.core.dao.BaseDao;
import com.qymerge.manage.data.dao.IYyueDao;
import com.qymerge.manage.data.dto.YyueDto;
import com.qymerge.manage.data.ro.YyueRO;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.util.*;

/**
 *<p>Title: YyueDaoImpl</p>
 *<p>Description: 预约记录</p>
 *
 *@Author cong.zhang
 *@Version 1.0.0
 *@Date 2020/12/19
 *@Time 17:54
 */
@Repository
public class YyueDaoImpl extends BaseDao implements IYyueDao {

    /**
     * 查询所有分页
     *
     * @param ro
     * @param pageRequest
     * @return
     */
    @Override
    public List list(YyueDto ro, PageRequest pageRequest) {
        int skip = (pageRequest.getPage() - 1) * pageRequest.getSize();
        int size = pageRequest.getSize();

        StringBuffer sql = new StringBuffer(ParamKey.NameSpace.TableSelect.yyue);
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(this.getJdbcTemplate());
        Map<String, Object> paramMap = new HashMap<String, Object>();

        if (ro.getId() > 0) {
            sql.append(" and id=:id");
            paramMap.put("id", ro.getId());
        }

        if (ro.getUserwxid() > 0) {
            sql.append(" and userwxid=:userwxid");
            paramMap.put("userwxid",ro.getUserwxid());
        }

        if (StringUtils.isNotBlank(ro.getUsername())) {
            sql.append(" and username like :username");
            paramMap.put("username", "%" + ro.getUsername() + "%");
        }

        if (StringUtils.isNotBlank(ro.getTel())) {
            sql.append(" and tel like :tel");
            paramMap.put("tel", "%" + ro.getTel() + "%");
        }

        if (StringUtils.isNotBlank(ro.getYdate())) {
            sql.append(" and ydate like :ydate");
            paramMap.put("ydate", "%" + ro.getYdate() + "%");
        }

        if (StringUtils.isNotBlank(ro.getYtime())) {
            sql.append(" and ytime like :ytime");
            paramMap.put("ytime", "%" + ro.getYtime() + "%");
        }

        if (StringUtils.isNotBlank(ro.getHxcode())) {
            sql.append(" and hxcode like :hxcode");
            paramMap.put("hxcode", "%" + ro.getHxcode() + "%");
        }

        if (ro.getIshx() > -1) {
            sql.append(" and ishx=:ishx");
            paramMap.put("ishx", ro.getIshx());
        }

        if (StringUtils.isNotBlank(ro.getHx_time_begin()) && StringUtils.isNotBlank(ro.getHx_time_end())) {
            sql.append(" and hxdate BETWEEN :hx_time_begin AND :hx_time_end");
            paramMap.put("hx_time_begin", ro.getHx_time_begin());
            paramMap.put("hx_time_end", ro.getHx_time_end());
        }

        if (StringUtils.isNotBlank(ro.getCreate_time_begin()) && StringUtils.isNotBlank(ro.getCreate_time_end())) {
            sql.append(" and creaetdate BETWEEN :create_time_begin AND :create_time_end");
            paramMap.put("create_time_begin", ro.getCreate_time_begin());
            paramMap.put("create_time_end", ro.getCreate_time_end());
        }

        if (StringUtils.isNotBlank(ro.getCourseType())) {
            sql.append(" and courseType like :courseType");
            paramMap.put("courseType", "%" + ro.getCourseType() + "%");
        }

        // 分页数据获取
        sql.append(" order by id desc limit :skip,:sizes");
        paramMap.put("skip", skip);
        paramMap.put("sizes", size);
        return jdbc.query(sql.toString(), paramMap, new BeanPropertyRowMapper<YyueRO>(YyueRO.class));
    }

    /**
     * 所有数量
     *
     * @param
     * @return
     */
    @Override
    public int total(YyueDto ro) {
        StringBuffer sql = new StringBuffer("select count(1) from yyue where 1=1");

        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(this.getJdbcTemplate());
        Map<String, Object> paramMap = new HashMap<String, Object>();

        if (ro.getId() > 0) {
            sql.append(" and id=:id");
            paramMap.put("id", ro.getId());
        }

        if (ro.getUserwxid() > 0) {
            sql.append(" and userwxid=:userwxid");
            paramMap.put("userwxid",ro.getUserwxid());
        }

        if (StringUtils.isNotBlank(ro.getUsername())) {
            sql.append(" and username like :username");
            paramMap.put("username", "%" + ro.getUsername() + "%");
        }

        if (StringUtils.isNotBlank(ro.getTel())) {
            sql.append(" and tel like :tel");
            paramMap.put("tel", "%" + ro.getTel() + "%");
        }

        if (StringUtils.isNotBlank(ro.getYdate())) {
            sql.append(" and ydate like :ydate");
            paramMap.put("ydate", "%" + ro.getYdate() + "%");
        }

        if (StringUtils.isNotBlank(ro.getYtime())) {
            sql.append(" and ytime like :ytime");
            paramMap.put("ytime", "%" + ro.getYtime() + "%");
        }

        if (StringUtils.isNotBlank(ro.getHxcode())) {
            sql.append(" and hxcode like :hxcode");
            paramMap.put("hxcode", "%" + ro.getHxcode() + "%");
        }

        if (ro.getIshx() > -1) {
            sql.append(" and ishx=:ishx");
            paramMap.put("ishx", ro.getIshx());
        }

        if (StringUtils.isNotBlank(ro.getHx_time_begin()) && StringUtils.isNotBlank(ro.getHx_time_end())) {
            sql.append(" and hxdate BETWEEN :hx_time_begin AND :hx_time_end");
            paramMap.put("hx_time_begin", ro.getHx_time_begin());
            paramMap.put("hx_time_end", ro.getHx_time_end());
        }

        if (StringUtils.isNotBlank(ro.getCreate_time_begin()) && StringUtils.isNotBlank(ro.getCreate_time_end())) {
            sql.append(" and creaetdate BETWEEN :create_time_begin AND :create_time_end");
            paramMap.put("create_time_begin", ro.getCreate_time_begin());
            paramMap.put("create_time_end", ro.getCreate_time_end());
        }

        if (StringUtils.isNotBlank(ro.getCourseType())) {
            sql.append(" and courseType like :courseType");
            paramMap.put("courseType", "%" + ro.getCourseType() + "%");
        }


        return jdbc.queryForObject(sql.toString(), paramMap, Integer.class);
    }

    @Override
    public YyueRO findOne(Long id) {
        StringBuffer sql = new StringBuffer(ParamKey.NameSpace.TableSelect.yyue);
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(this.getJdbcTemplate());
        Map<String, Object> paramMap = new HashMap<String, Object>();

        if (id > 0) {
            sql.append(" and id=:id");
            paramMap.put("id", id);
        }

        return jdbc.queryForObject(sql.toString(), paramMap, new BeanPropertyRowMapper<YyueRO>(YyueRO.class));
    }

    @Override
    public List<YyueRO> findAll(YyueDto ro) {
        StringBuffer sql = new StringBuffer(ParamKey.NameSpace.TableSelect.yyue);
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(this.getJdbcTemplate());
        Map<String, Object> paramMap = new HashMap<String, Object>();

        if (ro.getId() > 0) {
            sql.append(" and id=:id");
            paramMap.put("id", ro.getId());
        }

        if (ro.getUserwxid() > 0) {
            sql.append(" and userwxid=:userwxid");
            paramMap.put("userwxid",ro.getUserwxid());
        }

        if (StringUtils.isNotBlank(ro.getUsername())) {
            sql.append(" and username like :username");
            paramMap.put("username", "%" + ro.getUsername() + "%");
        }

        if (StringUtils.isNotBlank(ro.getTel())) {
            sql.append(" and tel like :tel");
            paramMap.put("tel", "%" + ro.getTel() + "%");
        }

        if (StringUtils.isNotBlank(ro.getYdate())) {
            sql.append(" and ydate like :ydate");
            paramMap.put("ydate", "%" + ro.getYdate() + "%");
        }

        if (StringUtils.isNotBlank(ro.getYtime())) {
            sql.append(" and ytime like :ytime");
            paramMap.put("ytime", "%" + ro.getYtime() + "%");
        }

        if (StringUtils.isNotBlank(ro.getHxcode())) {
            sql.append(" and hxcode like :hxcode");
            paramMap.put("hxcode", "%" + ro.getHxcode() + "%");
        }

        if (ro.getIshx() > -1) {
            sql.append(" and ishx=:ishx");
            paramMap.put("ishx", ro.getIshx());
        }

        if (StringUtils.isNotBlank(ro.getHx_time_begin()) && StringUtils.isNotBlank(ro.getHx_time_end())) {
            sql.append(" and hxdate BETWEEN :hx_time_begin AND :hx_time_end");
            paramMap.put("hx_time_begin", ro.getHx_time_begin());
            paramMap.put("hx_time_end", ro.getHx_time_end());
        }

        if (StringUtils.isNotBlank(ro.getCreate_time_begin()) && StringUtils.isNotBlank(ro.getCreate_time_end())) {
            sql.append(" and creaetdate BETWEEN :create_time_begin AND :create_time_end");
            paramMap.put("create_time_begin", ro.getCreate_time_begin());
            paramMap.put("create_time_end", ro.getCreate_time_end());
        }

        if (StringUtils.isNotBlank(ro.getCourseType())) {
            sql.append(" and courseType like :courseType");
            paramMap.put("courseType", "%" + ro.getCourseType() + "%");
        }

        sql.append(" order by id desc");
        return jdbc.query(sql.toString(), paramMap, new BeanPropertyRowMapper<YyueRO>(YyueRO.class));
    }

    @Override
    public int install(YyueRO ro) {
        StringBuilder sql = new StringBuilder("insert into yyue(userwxid,username,tel,ydate,ytime,hecode,hxcode,ishx,hxdate,createdate) values(?,?,?,?,?,?,?,?,?,?)");

        List params = new ArrayList();
        params.add(ro.getUserwxid());
        params.add(ro.getUsername());
        params.add(ro.getTel());
        params.add(ro.getYdate());
        params.add(ro.getYtime());
        params.add(ro.getHecode());
        params.add(ro.getHxcode());
        params.add(ro.getIshx());
        params.add(ro.getHxdate());
        params.add(ro.getCreatedate());

        return getJdbcTemplate().update(sql.toString(), params.toArray());
    }

    @Override
    public int update(YyueRO ro) {
        StringBuilder builder = new StringBuilder("update yyue set userwxid=?,username=?,tel=?,ydate=?,ytime=?,hecode=?,hxcode=?,ishx=?,hxdate=?,createdate=? ");

        List params = new LinkedList();
        params.add(ro.getUserwxid());
        params.add(ro.getUsername());
        params.add(ro.getTel());
        params.add(ro.getYdate());
        params.add(ro.getYtime());
        params.add(ro.getHecode());
        params.add(ro.getHxcode());
        params.add(ro.getIshx());
        params.add(ro.getHxdate());
        params.add(ro.getCreatedate());

        builder.append("where id=?");
        params.add(ro.getId());
        return getJdbcTemplate().update(builder.toString(), params.toArray());
    }

    @Override
    public int remove(List<Long> ids) {
        // 删除
        String sql="delete from yyue where id=?";
        final List<Long> idss = ids;
        BatchPreparedStatementSetter setterMid = new BatchPreparedStatementSetter(){

            @Override
            public int getBatchSize(){
                return idss.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i){
                Long id = idss.get(i);
                try {
                    ps.setLong(1, id);
                } catch(Exception e){
                    logger.error("batchInsertWaitAward Exception:", e);
                }
            }
        };
        return getJdbcTemplate().batchUpdate(sql, setterMid).length;
    }

    @Override
    public int updateIshx(long id, int ishx) {
        StringBuilder builder = new StringBuilder("update yyue set ishx=? ");
        builder.append("where id=?");
        return getJdbcTemplate().update(builder.toString(), ishx);
    }


}
